﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Text;

namespace PropertyExpression.QueryExecutor
{
    public static class QueryExecutor
    {
        public static IEnumerable<TEntity> Execute<TEntity>(
            Action<IQueryContext> queryContextAction) where TEntity : new() 
        {
            DataTable dataTable;

            using (var connection = new SqlConnection(@"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"))
            {
                connection.Open();
                var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    //TODO: Improve perfomance: using DataReader instead DataAdapter
                    var dataAdapter =
                        new SqlDataAdapter
                            {
                                SelectCommand = new SqlCommand
                                                    {
                                                        Connection = connection,
                                                        Transaction = transaction
                                                    }
                            };

                    var queryContext = new QueryContext(dataAdapter.SelectCommand);
                    queryContextAction(queryContext);

                    dataAdapter.SelectCommand.CommandText = queryContext.Builder.ToString();

                    GenerateClass<TEntity>(dataAdapter.SelectCommand);

                    var dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    dataTable = dataSet.Tables[0];

                    transaction.Commit();
                }
                catch (Exception exception)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {
                        throw new ApplicationException("The transaction can not be rolled back.", exception);
                    }
                    throw;
                }
            }

            var result = new LinkedList<TEntity>();
            foreach (DataRow dataRow in dataTable.Rows)
            {
                var resultItem = new TEntity();
                foreach (DataColumn dataColumn in dataTable.Columns)
                {
                    //TODO: Improve perfomance: use cache. And for reflection.
                    var propertyInfo = typeof (TEntity).GetProperty(dataColumn.ColumnName);
                    if (propertyInfo == null)
                    {
                        throw new ApplicationException();
                    }
                    propertyInfo.SetValue(resultItem, GetValue(propertyInfo, dataColumn, dataRow), null);
                }
                result.AddLast(resultItem);
            }
            return result;
        }

        private static void GenerateClass<TEntity>(SqlCommand sqlCommand)
        {
            var builder = new StringBuilder();
            using (var sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                foreach (DataRow dataRow in sqlDataReader.GetSchemaTable().Rows)
                {
                    var dataType = (Type) dataRow["DataType"];
                    string nullable;
                    if (false.Equals(dataRow["AllowDBNull"]))
                    {
                        nullable = string.Empty;
                    }
                    else
                    {
                        if (!dataType.IsClass)
                        {
                            nullable = "?";
                        }
                        else
                        {
                            nullable = string.Empty;
                        }
                    }
                    builder.AppendFormat(@"
    public {0}{2} {1} {{ get; set; }}",
                                         dataType,
                                         dataRow["ColumnName"],
                                         nullable);
                }
            }

            var output = string.Format(@"public class {0}
{{{1}
}}",
                                       typeof (TEntity).Name,
                                       builder);
            Console.WriteLine(output);
            File.WriteAllText(
                @"C:\Temp\GeneratedClass.txt",
                output);
        }

        private static object GetValue(PropertyInfo propertyInfo, DataColumn dataColumn, DataRow dataRow)
        {
            //TODO: можно еще в случае NULL не выставлять значение, оно уже в объекте и так NULL
            var value = dataRow[dataColumn];
            if (value == DBNull.Value)
            {
                var propertyType = propertyInfo.PropertyType;
                if (propertyType.IsClass)
                {
                    return null;
                }
                else
                {
                    if (propertyType.IsGenericType)
                    {
                        if (propertyType.GetGenericTypeDefinition().Equals(typeof (Nullable<>)))
                        {
                            return null;
                        }
                    }
                    throw new ApplicationException();
                }
            }
            else
            {
                return value;              
            }
        }

        private class QueryContext : IQueryContext
        {
            private readonly SqlCommand sqlCommand;
            private readonly StringBuilder builder = new StringBuilder();

            public QueryContext(SqlCommand sqlCommand)
            {
                this.sqlCommand = sqlCommand;
            }

            public StringBuilder Builder
            {
                get
                {
                    //TODO: передавать в конструкторе Func
                    return builder;
                }
            }

            public void AddParameter(string parameterName, object parameterValue)
            {
                //TODO: передавать в конструкторе Func
                sqlCommand.Parameters.AddWithValue(parameterName, parameterValue);
            }
        }
    }
}